package com.example.demo.web.dao;

import java.util.List;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.example.demo.web.Entity.GateWayEntity;
import com.example.demo.web.Entity.OrganizationEntity;
import com.example.demo.web.util.XinLongUtil;


@Repository
public class GatewayDAO {
	private Logger log = LogManager.getLogger(GatewayDAO.class);
	private JdbcTemplate jdbcTemplate;

    @Autowired
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

    public List<GateWayEntity> findAll(int pagenum,int pagesize,String orgname,String mac,List<OrganizationEntity> orgs){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or tlock.orgid = '" + org.getId()+ "'");
    		}
    		i++;
    	}
    	String sql = "SELECT tlock.*, org.Name as orgname FROM tbl_gateway tlock left join tbl_organization org on org.id = tlock.orgid where active < 2 and (tlock.orgid = '" + sb.toString() + ")";
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'"; 
    	}
    	if(!mac.equals("")) {
    		sql += " and tlock.mac like '%" + mac + "'"; 
    	}
    	sql += " order by org.Orgcode limit " + (pagenum-1)*pagesize + "," + pagesize;   
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(GateWayEntity.class));
    }
    
    public List<GateWayEntity> findAll(){
    	String sql = "SELECT * FROM tbl_gateway";       
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(GateWayEntity.class));
    }
    
    public int getCount(String orgname,String mac,List<OrganizationEntity> orgs){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or tlock.orgid = '" + org.getId()+ "'");
    		}
    		i++;
    	}
    	String sql = "SELECT count(*) FROM tbl_gateway tlock left join tbl_organization org on org.id = tlock.orgid where active < 2 and (tlock.orgid = '" + sb.toString() + ")";
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'"; 
    	}
    	if(!mac.equals("")) {
    		sql += " and tlock.mac like '%" + mac + "'"; 
    	}
    	return jdbcTemplate.queryForObject(sql, Integer.class);
    }
    
    public GateWayEntity findByMac(String mac){
    	String sql = "SELECT * FROM tbl_gateway WHERE mac='" + mac + "'";
        try{
        	return  jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(GateWayEntity.class));
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return null;
        }
    	
    }  
    
    public int updateGateway(GateWayEntity gateway){

		// update
		String sql = "UPDATE tbl_gateway SET "
					+ "area=?, orgid=?, regdate=?,owner=?, reguser=?, active=?, cname=?, offdate=?, offuser=?,descriptor=?,model=?,factory=?"
					+ ",version=? ,ethernet_ip=? WHERE mac=?";
		try{
			jdbcTemplate.update(sql, gateway.getArea(),
					gateway.getOrgid(), gateway.getRegdate(),gateway.getOwner(),gateway.getReguser(),gateway.getActive(),gateway.getCname(),gateway.getOffdate(),gateway.getOffuser(),gateway.getDescriptor()
					,gateway.getModel(),gateway.getFactory(),gateway.getVersion(),gateway.getEthernet_ip(),gateway.getMac());
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return 0;
		}
		return 1;
    	
    }
    
    public int saveGateway(GateWayEntity gateway){    	
		// insert
		String sql = "INSERT INTO tbl_gateway (devcode,mac, area,orgid,regdate,reguser,active,owner,cname,offdate,offuser,descriptor,model,factory,version,ethernet_ip)"
                + " VALUES (?, ?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try{
			jdbcTemplate.update(sql, gateway.getDevcode(),gateway.getMac(),
					gateway.getArea(), gateway.getOrgid(), gateway.getRegdate(),gateway.getReguser(),gateway.getActive(),gateway.getOwner(),gateway.getCname(),gateway.getOffdate(),gateway.getOffuser(),gateway.getDescriptor(),
					gateway.getModel(),gateway.getFactory(),gateway.getVersion(),gateway.getEthernet_ip());
		}catch(DuplicateKeyException dx){
			XinLongUtil.xinLongUtil.printlog(log,dx.getMessage());
			return 2;
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return 0;
		}
    	return 1;
    	
    	
    }
    
    public int delete(String mac) {
        String sql = "DELETE FROM tbl_gateway WHERE mac=?";
        try{
        	return jdbcTemplate.update(sql, mac);
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return 0;
        }
        
    }
    
    public int offGateway(String mac,String offdate,String offuser){

		// update
		String sql = "UPDATE tbl_gateway SET active=?,offtime=?,offuser=? WHERE mac=?";
		try{
			jdbcTemplate.update(sql, 99,offdate,offuser, mac);
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return 0;
		}
		return 1;
    	
    }
    
    public List<GateWayEntity> findOffAll(int pagenum,int pagesize,String orgname,String mac,List<OrganizationEntity> orgs){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or tlock.orgid = '" + org.getId()+ "'");
    		}
    		i++;
    	}
    	String sql = "SELECT tlock.*, org.Name as orgname FROM tbl_gateway tlock left join tbl_organization org on org.id = tlock.orgid where active = 99 and (tlock.orgid = '" + sb.toString() + ")";
    	if(!orgname.equals("")) {
    		sql += " and org.Name LIKE '%" + orgname + "%'"; 
    	}
    	if(!mac.equals("")) {
    		sql += " and tlock.mac like '%" + mac + "'"; 
    	}
    	sql += " order by org.Orgcode limit " + (pagenum-1)*pagesize + "," + pagesize;   
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(GateWayEntity.class));
    }
    
    public int getOffCount(String orgname,String mac,List<OrganizationEntity> orgs){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or tlock.orgid = '" + org.getId()+ "'");
    		}
    		i++;
    	}
    	String sql = "SELECT count(*) FROM tbl_gateway tlock left join tbl_organization org on org.id = tlock.orgid where active = 99 and (tlock.orgid = '" + sb.toString() + ")";
    	if(!orgname.equals("")) {
    		sql += " and org.Name LIKE '%" + orgname + "%'"; 
    	}
    	if(!mac.equals("")) {
    		sql += " and tlock.locksn like '%" + mac + "'"; 
    	}
    	return jdbcTemplate.queryForObject(sql, Integer.class);
    }
    
}
